﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;

namespace Data {
    // Create, open and return sqlConnecion if ok, unless return null
    public abstract class DatabaseProvider {
        #region Fields

        private const string DatabaseName = "Data.db3";

        private const string DataCreator =
            @"CREATE TABLE [CommonValue] (
                                            [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                            [value] text NOT NULL );
                                            CREATE TABLE [Language] (
                                            [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                            [name] nvarchar(50) NOT NULL);
                                            CREATE TABLE [Word] (
                                            [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                                            [key] nvarchar(50) NOT NULL,
                                            [value] text,
                                            [commonvalue_id] integer NOT NULL,
                                            [lang_id] nvarchar(50) NOT NULL,
                                            [enable] bit NOT NULL DEFAULT 1 )";

        #endregion

        public static SQLiteConnection CreateConnection() {
            // Create db if it doesn't exist
            string dataPath = @".\Data\";
            string connString = @"Data Source=" + dataPath + DatabaseName;
            //return new SQLiteConnection(connString);
            SQLiteConnection cnn;

            if (!File.Exists(dataPath + DatabaseName)) {
                // Create folder if it doesn't exist
                if (!Directory.Exists(dataPath))
                    Directory.CreateDirectory(dataPath);
                // Create database
                SQLiteConnection.CreateFile(dataPath + DatabaseName);
                cnn = new SQLiteConnection(connString);
                cnn.Open();
                SQLiteCommand cmd = cnn.CreateCommand();
                cmd.CommandText = DataCreator;
                cmd.ExecuteNonQuery();
                cnn.Close();
            }
            else {
                cnn = new SQLiteConnection(connString);
            }

            return cnn;
        }

        // SQliteExecuteScalar
        public static object SqliteExecuteScalar(string q, SQLiteParameter[] pColl) {
            // If there's no parameter, insert NULL
            Object obj;
            SQLiteConnection cnn = CreateConnection();
            cnn.Open();
            using (DbTransaction dbTrans = cnn.BeginTransaction()) {
                using (DbCommand cmd = cnn.CreateCommand()) {
                    cmd.CommandText = q;
                    if (pColl != null)
                        cmd.Parameters.AddRange(pColl);
                    obj = cmd.ExecuteScalar();

                    cmd.Dispose();
                }
                dbTrans.Commit();
                dbTrans.Dispose();
            }
            cnn.Close();
            cnn.Dispose();
            return obj;
        }

        // SQliteExecuteNonQuery
        public static object SqliteExecuteNonQuery(string q, SQLiteParameter[] pColl) {
            // If there's no parameter, insert NULL
            Object obj;
            SQLiteConnection cnn = CreateConnection();
            cnn.Open();
            using (DbTransaction dbTrans = cnn.BeginTransaction()) {
                using (DbCommand cmd = cnn.CreateCommand()) {
                    cmd.CommandText = q;
                    cmd.Parameters.AddRange(pColl);
                    obj = cmd.ExecuteNonQuery();

                    cmd.Dispose();
                }
                dbTrans.Commit();
                dbTrans.Dispose();
            }
            cnn.Close();
            cnn.Dispose();
            return obj;
        }

        // SQliteDataReader
        public static List<object> SqliteDataReader(string q, SQLiteParameter[] sqLiteParameters) {
            // If there's no parameter, insert NULL
            SQLiteDataReader dataReader;
            var objects = new List<object>();
            SQLiteConnection cnn = CreateConnection();
            cnn.Open();
            using (DbTransaction dbTrans = cnn.BeginTransaction()) {
                using (DbCommand cmd = cnn.CreateCommand()) {
                    cmd.CommandText = q;
                    if (sqLiteParameters != null)
                        cmd.Parameters.AddRange(sqLiteParameters);
                    dataReader = (SQLiteDataReader) cmd.ExecuteReader();
                    while (dataReader.Read()) {
                        objects.Add(dataReader[0]);
                    }
                    dataReader.Close();
                }
                dbTrans.Commit();
            }
            cnn.Close();
            return objects;
        }
    }
}